const router = require('koa-router')()
const path = require("path");
const fs = require("fs")

router.prefix('/information')
const courseImgPath = path.join(path.resolve(__dirname, '../../../..'), '/public/images/course_imgs');
console.log(courseImgPath, 'at course.js');
// 将标准时间转换为YYYY-MM-DD HH:MM:SS类型的
const formTime = (time) => {
    let newtime = new Date(time);
    // console.log(new Date(time), "要进行转换的时间");
    const year = newtime.getFullYear();
    const month = String(newtime.getMonth() + 1).padStart(2, "0");
    const day = String(newtime.getDate()).padStart(2, "0");
    const hours = String(newtime.getHours()).padStart(2, "0");
    const minutes = String(newtime.getMinutes()).padStart(2, "0");
    const seconds = String(newtime.getSeconds()).padStart(2, "0");

    const formattedDate = `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
    return formattedDate;
}
// 获取信息
router.get("/getCourse", async (ctx, next) => {
    console.log(ctx.query);
    let query = ctx.query;
    let sql = "select course.course_id,course.course_name,course.course_introduction,course.course_image,course.course_size,course.course_credit,course.apply_start_date,course.apply_end_date,course.start_date,course.end_date,institute.institute_id,institute.institute_name,grade.grade_id,grade.grade_name,teacher.teacher_id,teacher.teacher_name from course join institute on institute.institute_id = course.institute_id join grade on grade.grade_id = course.grade_id join teacher on teacher.teacher_id = course.teacher_id limit ? offset ?";
    let res = await ctx.db.EXCUTE(sql, [1 * query.pageSize, query.pageSize * (query.pageNum - 1)])
    // let list = res.map(item => {
    //     return { ...item, apply_start_date: ctx.formTime.formatDate(item.apply_start_date), apply_end_date: ctx.formTime.formatDate(item.apply_end_date), start_date: ctx.formTime.formatDate(item.start_date), end_date: ctx.formTime.formatDate(item.end_date) }
    // })
    let numSql = 'select COUNT(*) as num from course'
    let numRes = await ctx.db.EXCUTE(numSql, [])
    ctx.body = { code: 200, msg: "查询成功", data: { items: res, total: numRes[0].num } }
})
// 搜索信息
router.post('/searchCourse', async (ctx, next) => {
    let query = ctx.query;
    let data = ctx.request.body;
    if (data.institute_id == '' && data.grade_id == '') {
        let sql = `select course.course_id,course.course_name,course.course_introduction,course.course_image,course.course_size,course.course_credit,course.apply_start_date,course.apply_end_date,course.start_date,course.end_date,institute.institute_id,institute.institute_name,grade.grade_id,grade.grade_name,teacher.teacher_id,teacher.teacher_name from course join institute on institute.institute_id = course.institute_id join grade on grade.grade_id = course.grade_id join teacher on teacher.teacher_id = course.teacher_id where course.course_name LIKE '%${data.keyword}%'`;
        let res = await ctx.db.EXCUTE(sql, [])
        // let list = res.map(item => {
        //     return { ...item, apply_start_date: ctx.formTime.formatDate(item.apply_start_date), apply_end_date: ctx.formTime.formatDate(item.apply_end_date), start_date: ctx.formTime.formatDate(item.start_date), end_date: ctx.formTime.formatDate(item.end_date) }
        // })

        ctx.body = { code: 200, msg: "查询成功", data: { items: res.slice((query.pageNum - 1) * query.pageSize, query.pageNum * query.pageSize), total: res.length } }
    }
    if (data.institute_id == '' && data.grade_id != '') {
        let sql = `select course.course_id,course.course_name,course.course_introduction,course.course_image,course.course_size,course.course_credit,course.apply_start_date,course.apply_end_date,course.start_date,course.end_date,institute.institute_id,institute.institute_name,grade.grade_id,grade.grade_name,teacher.teacher_id,teacher.teacher_name from course join institute on institute.institute_id = course.institute_id join grade on grade.grade_id = course.grade_id join teacher on teacher.teacher_id = course.teacher_id where course.course_name LIKE '%${data.keyword}%' and course.grade_id=?`;
        let res = await ctx.db.EXCUTE(sql, [data.grade_id])
        // let list = res.map(item => {
        //     return { ...item, apply_start_date: ctx.formTime.formatDate(item.apply_start_date), apply_end_date: ctx.formTime.formatDate(item.apply_end_date), start_date: ctx.formTime.formatDate(item.start_date), end_date: ctx.formTime.formatDate(item.end_date) }
        // })

        ctx.body = { code: 200, msg: "查询成功", data: { items: res.slice((query.pageNum - 1) * query.pageSize, query.pageNum * query.pageSize), total: res.length } }
    }
    if (data.institute_id != '' && data.grade_id == '') {
        let sql = `select course.course_id,course.course_name,course.course_introduction,course.course_image,course.course_size,course.course_credit,course.apply_start_date,course.apply_end_date,course.start_date,course.end_date,institute.institute_id,institute.institute_name,grade.grade_id,grade.grade_name,teacher.teacher_id,teacher.teacher_name from course join institute on institute.institute_id = course.institute_id join grade on grade.grade_id = course.grade_id join teacher on teacher.teacher_id = course.teacher_id where course.course_name LIKE '%${data.keyword}%' and course.institute_id=?`;
        let res = await ctx.db.EXCUTE(sql, [data.institute_id])
        // let list = res.map(item => {
        //     return { ...item, apply_start_date: ctx.formTime.formatDate(item.apply_start_date), apply_end_date: ctx.formTime.formatDate(item.apply_end_date), start_date: ctx.formTime.formatDate(item.start_date), end_date: ctx.formTime.formatDate(item.end_date) }
        // })

        ctx.body = { code: 200, msg: "查询成功", data: { items: res.slice((query.pageNum - 1) * query.pageSize, query.pageNum * query.pageSize), total: res.length } }
    }
    if (data.institute_id != '' && data.grade_id != '') {
        let sql = `select course.course_id,course.course_name,course.course_introduction,course.course_image,course.course_size,course.course_credit,course.apply_start_date,course.apply_end_date,course.start_date,course.end_date,institute.institute_id,institute.institute_name,grade.grade_id,grade.grade_name,teacher.teacher_id,teacher.teacher_name from course join institute on institute.institute_id = course.institute_id join grade on grade.grade_id = course.grade_id join teacher on teacher.teacher_id = course.teacher_id where course.course_name LIKE '%${data.keyword}%' and course.institute_id=? and course.grade_id=?`;
        let res = await ctx.db.EXCUTE(sql, [data.institute_id, data.grade_id])
        // let list = res.map(item => {
        //     return { ...item, apply_start_date: ctx.formTime.formatDate(item.apply_start_date), apply_end_date: ctx.formTime.formatDate(item.apply_end_date), start_date: ctx.formTime.formatDate(item.start_date), end_date: ctx.formTime.formatDate(item.end_date) }
        // })

        ctx.body = { code: 200, msg: "查询成功", data: { items: res.slice((query.pageNum - 1) * query.pageSize, query.pageNum * query.pageSize), total: res.length } }
    }
})
// 搜索老师信息
router.post('/searchTeacher', async (ctx, next) => {
    let query = ctx.query;
    let data = ctx.request.body;
    let sql = `select * from teacher where institute_id=?`;
    let res = await ctx.db.EXCUTE(sql, [data.institute_id])

    ctx.body = { code: 200, msg: "查询成功", data: { items: res } }
    // }
})
// 添加课程
router.post('/addCourse', async (ctx, next) => {
    let data = ctx.request.body
    console.log(data.recipient);
    console.log(ctx.state.user.user_id);
    let Fsql = 'select course_name from course where teacher_id=? and course_name=?'
    let Fres = await ctx.db.EXCUTE(Fsql, [data.teacher_id, data.course_name])
    console.log(data, "打印出来的data信息");
    if (Fres.length == 0) {
        // 此处的创建学生ID传入的为1
        let id = ctx.snowFlake.createSnow(5);
        let sql = 'insert into course (course_id,grade_id,course_name,course_introduction,teacher_id,institute_id,course_size,course_credit,apply_start_date,apply_end_date,start_date,end_date) values (?,?,?,?,?,?,?,?,?,?,?,?)'
        let res = await ctx.db.EXCUTE(sql, [id, data.grade_id, data.course_name, data.course_desc, data.teacher_id, data.institute_id, data.course_size, data.course_credit, data.apply_start_date, data.apply_end_date, data.start_date, data.end_date])
        console.log(res);
        // let msql = 'insert into message (message_id, sender, recipient, content, status, create_time, update_time, type) values (?,?,?,?,?,?,?,?)'
        // let mres = await ctx.db.EXCUTE(msql, [id, ctx.state.user.user_id, data.recipient, '给您添加了一门新课程--' + data.course_name, 'unread', formTime(new Date()), formTime(new Date()), 'info'])
        if (res.affectedRows != 0) {
            ctx.body = { code: 200, msg: "添加成功" }
        } else {
            ctx.body = { code: 204, msg: "mysql error" }
        }
    } else {
        ctx.body = { code: 201, msg: '该老师已经任教了该课程，请确认无误后再重试' }
    }
})
// 修改课程
router.post("/editCourse", async (ctx, next) => {
    const data = ctx.query;
    let file = ctx.request.body
    console.log(data);
    console.log(file);
    // console.log(ctx.request.body.get("avatar"), "avatar");
    // 在修改课程名的时候需要进行课程名与老师的判定，相同则不允许更改
    let courseNameSql = 'select course_name from course where course_name = ? and course_id != ?'
    let sql = "update course set teacher_id = ?,course_name = ?,course_credit = ?,course_size = ?,course_introduction = ?,apply_start_date = ?,apply_end_date = ?,start_date = ?,end_date = ? where course_id = ?";
    let courseNameRes = await ctx.db.EXCUTE(courseNameSql, [data.user_name, data.user_id]);
    console.log(courseNameRes, "查询是否存在相同课程");
    if (courseNameRes.length === 0) {
        let res = await ctx.db.EXCUTE(sql, [data.teacher_id, data.course_name, data.course_credit, data.course_size, data.course_introduction, data.apply_start_date, data.apply_end_date, data.start_date, data.end_date, data.course_id]);
        if (data.recipient0 != data.recipient1) {
            let msql = 'insert into message (message_id, sender, recipient, content, status, create_time, update_time, type) values (?,?,?,?,?,?,?,?)'
            let mres = await ctx.db.EXCUTE(msql, [ctx.snowFlake.createSnow(5), ctx.state.user.user_id, data.recipient0, '管理员把 ' + data.course_name + ' 课程分配给了其他老师', 'unread', formTime(new Date()), formTime(new Date()), 'info'])
            let mres1 = await ctx.db.EXCUTE(msql, [ctx.snowFlake.createSnow(5), ctx.state.user.user_id, data.recipient1, '给您分配了一门新课程--' + data.course_name, 'unread', formTime(new Date()), formTime(new Date()), 'info'])
        }
        ctx.body = { code: 200, ...ctx.upload.uploadCourseImg(ctx, courseImgPath, data.course_id), msg: "修改成功" }
    } else {
        ctx.body = { code: 500, msg: "该课程已存在" }
    }
})
// 删除课程
router.post('/deleteCourse', async (ctx, next) => {
    let data = ctx.request.body
    let imgSql = 'select course_image from course where course_id=?'
    let imgRes = await ctx.db.EXCUTE(imgSql, [data.course_id])
    console.log(imgRes);
    let sql = 'delete from course where course_id=?'
    let res = await ctx.db.EXCUTE(sql, [data.course_id])
    console.log(res);
    if (res.affectedRows != 0 && imgRes[0].course_image) {
        fs.stat(path.join(courseImgPath, imgRes[0].course_image), (err, state) => {
            if (err) {
                console.log(err);
                throw new Error(err.message);
            } else {
                fs.unlinkSync(path.join(courseImgPath, imgRes[0].course_image));
            }
        })
    }
    ctx.body = { code: 200, msg: '删除成功' }
})

module.exports = router